from sqlalchemy.orm import  sessionmaker, aliased
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Text, String, TIMESTAMP,SmallInteger
from sqlalchemy import create_engine
from sqlalchemy.sql import func
import datetime
Base = declarative_base() # 创建orm的基类

class Actor(Base):
    __tablename__ = 'actor'
    actor_id = Column(SmallInteger, primary_key=True, autoincrement=True)
    first_name = Column(String(45), nullable=False)
    last_name = Column(String(45), nullable=False)
    last_update = Column(TIMESTAMP, nullable=False, default=datetime.datetime.now())

class Actor_info(Base):
    __tablename__ = 'actor_info'
    actor_id = Column(SmallInteger, nullable=False, primary_key=True)
    first_name = Column(String(45), nullable=False)
    last_name = Column(String(45), nullable=False)
    film_info = Column(Text, primary_key=True)



class OrmDB:
    # 构造函数，初始化Orm的session
    def __init__(self):
        self.engine = create_engine('mysql+pymysql://root:123456@localhost:3306/mydb?charset=utf8')
        maker = sessionmaker(bind=self.engine)
        self.session = maker()  # 实例化session对象

    def findById(self):
        return self.session.query(Actor.actor_id,Actor.first_name,Actor.last_name,Actor.last_update,Actor_info.film_info).filter(Actor.actor_id==Actor_info.actor_id).all()

    def findByOption(self):
        actor_alis = aliased(Actor,name='actor_alis')
        return self.session.query(actor_alis.last_update, func.count(actor_alis.actor_id)).filter(actor_alis.actor_id==Actor_info.actor_id).group_by(actor_alis.last_update).order_by(actor_alis.last_update).all()

if __name__ == '__main__':

    orm = OrmDB()
    # ls = orm.findById()
    ls = orm.findByOption()
    print(ls)
    for item in ls:
        print(item.last_update)



